package com.ftg.learn.bus.sharecar_expendrecord.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.ftg.learn.bus.sharecar_expendrecord.pojo.sharecar_expendrecord_admin;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;


import java.util.List;
import java.util.Map;

@Mapper
public interface Sharecar_Expendrecord_Admin_Mapper extends BaseMapper<sharecar_expendrecord_admin> {

    /**
     * 查全部审核状态为非特殊情况
     * @return
     */
    @Select("select * from Sharecar_Expendrecord_Admin where check_state NOT IN ('特殊情况');")
    List<sharecar_expendrecord_admin> query();

    /**
     * 查一条
     * @return
     */
    @Select("select * from Sharecar_Expendrecord_Admin where expendcar_id = #{expendcar_id} and check_state NOT IN ('特殊情况');")
    sharecar_expendrecord_admin findOne(String expendcar_id);

    @Select("SELECT  column_name,column_comment FROM information_schema.columns " +
            "WHERE table_schema ='system' AND  table_name = 'sharecar_expendrecord_admin'")
    List<Map<String, Object>> selectColumnNameAndComment();

    /**
     * 公车费用统计
     * @return
     */
    @Select("SELECT car_id,cost_type,SUM(ifnull(cost_money,0)) AS 总费用 FROM sharecar_expendrecord_admin se  GROUP BY cost_type,car_id")
    List<Map<String,Object>> costTable();

    /**
     * 发票
     * @return
     */
    @Select("SELECT expendcar_id,cost_type,cost_money,manufacturer,invoice_number,(SELECT SUM(ifnull(cost_money,0)) FROM sharecar_expendrecord_admin WHERE car_id = #{car_id})"
            + "FROM sharecar_expendrecord_admin WHERE car_id = #{car_id}")
    List<Map<String,Object>> invoice(String car_id);

    @Select("select * from Sharecar_Expendrecord_Admin")
    List<Map<String,Object>> find();
}
